SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- 文件表
-- ----------------------------
DROP TABLE IF EXISTS lin_file;
CREATE TABLE lin_file
(
    id          int(10) unsigned NOT NULL AUTO_INCREMENT,
    path        varchar(500)     NOT NULL,
    type        varchar(10)      NOT NULL DEFAULT 'LOCAL' COMMENT 'LOCAL 本地，REMOTE 远程',
    name        varchar(100)     NOT NULL,
    extension   varchar(50)               DEFAULT NULL,
    size        int(11)                   DEFAULT NULL,
    md5         varchar(40)               DEFAULT NULL COMMENT 'md5值，防止上传重复文件',
    create_time datetime(3)      NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_time datetime(3)      NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    delete_time datetime(3)               DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY md5_del (md5, delete_time)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

-- ----------------------------
-- 日志表
-- ----------------------------
DROP TABLE IF EXISTS lin_log;
CREATE TABLE lin_log
(
    id          int(10) unsigned NOT NULL AUTO_INCREMENT,
    message     varchar(450)              DEFAULT NULL,
    user_id     int(10) unsigned NOT NULL,
    username    varchar(24)               DEFAULT NULL,
    status_code int(11)                   DEFAULT NULL,
    method      varchar(20)               DEFAULT NULL,
    path        varchar(50)               DEFAULT NULL,
    permission  varchar(100)              DEFAULT NULL,
    create_time datetime(3)      NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_time datetime(3)      NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    delete_time datetime(3)               DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

-- ----------------------------
-- 权限表
-- ----------------------------
DROP TABLE IF EXISTS lin_permission;
CREATE TABLE lin_permission
(
    id          int(10) unsigned NOT NULL AUTO_INCREMENT,
    name        varchar(60)      NOT NULL COMMENT '权限名称，例如：访问首页',
    module      varchar(50)      NOT NULL COMMENT '权限所属模块，例如：人员管理',
    mount       tinyint(1)       NOT NULL DEFAULT 1 COMMENT '0：关闭 1：开启',
    create_time datetime(3)      NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_time datetime(3)      NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    delete_time datetime(3)               DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

-- ----------------------------
-- 分组表
-- ----------------------------
DROP TABLE IF EXISTS lin_group;
CREATE TABLE lin_group
(
    id          int(10) unsigned NOT NULL AUTO_INCREMENT,
    name        varchar(60)      NOT NULL COMMENT '分组名称，例如：搬砖者',
    info        varchar(255)              DEFAULT NULL COMMENT '分组信息：例如：搬砖的人',
    level       tinyint(2)       NOT NULL DEFAULT 3 COMMENT '分组级别 1：root 2：guest 3：user（root、guest分组只能存在一个)',
    create_time datetime(3)      NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_time datetime(3)      NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    delete_time datetime(3)               DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY name_del (name, delete_time)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

-- ----------------------------
-- 分组-权限表
-- ----------------------------
DROP TABLE IF EXISTS lin_group_permission;
CREATE TABLE lin_group_permission
(
    id            int(10) unsigned NOT NULL AUTO_INCREMENT,
    group_id      int(10) unsigned NOT NULL COMMENT '分组id',
    permission_id int(10) unsigned NOT NULL COMMENT '权限id',
    PRIMARY KEY (id),
    KEY group_id_permission_id (group_id, permission_id) USING BTREE COMMENT '联合索引'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

-- ----------------------------
-- 用户基本信息表
-- ----------------------------
DROP TABLE IF EXISTS lin_user;
CREATE TABLE lin_user
(
    id          int(10) unsigned NOT NULL AUTO_INCREMENT,
    username    varchar(24)      NOT NULL COMMENT '用户名，唯一',
    nickname    varchar(24)               DEFAULT NULL COMMENT '用户昵称',
    avatar      varchar(500)              DEFAULT NULL COMMENT '头像url',
    email       varchar(100)              DEFAULT NULL COMMENT '邮箱',
    create_time datetime(3)      NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_time datetime(3)      NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    delete_time datetime(3)               DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY username_del (username, delete_time),
    UNIQUE KEY email_del (email, delete_time)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

-- ----------------------------
-- 用户授权信息表
# id
# user_id
# identity_type 登录类型（手机号 邮箱 用户名）或第三方应用名称（微信 微博等）
# identifier 标识（手机号 邮箱 用户名或第三方应用的唯一标识）
# credential 密码凭证（站内的保存密码，站外的不保存或保存token）
-- ----------------------------
DROP TABLE IF EXISTS lin_user_identity;
CREATE TABLE lin_user_identity
(
    id            int(10) unsigned NOT NULL AUTO_INCREMENT,
    user_id       int(10) unsigned NOT NULL COMMENT '用户id',
    identity_type varchar(100)     NOT NULL,
    identifier    varchar(100),
    credential    varchar(100),
    create_time   datetime(3)      NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_time   datetime(3)      NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    delete_time   datetime(3)               DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

DROP TABLE IF EXISTS book;
CREATE TABLE book
(
    id          int(11)     NOT NULL AUTO_INCREMENT,
    title       varchar(50) NOT NULL,
    author      varchar(30)          DEFAULT NULL,
    summary     varchar(1000)        DEFAULT NULL,
    image       varchar(100)         DEFAULT NULL,
    create_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    delete_time datetime(3)          DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

-- ----------------------------
-- 机构表
-- ----------------------------
DROP TABLE IF EXISTS organization;
CREATE TABLE organization
(
    id          int(11)    NOT NULL AUTO_INCREMENT,
    name        varchar(20) NOT NULL COMMENT '名称',
    used_num     int(11) NOT NULL DEFAULT 0 COMMENT '已使用点数',
    remain_num  int(11) NOT NULL COMMENT '剩余点数',
    create_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    delete_time datetime(3)          DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

-- ----------------------------
-- 学员信息表
-- ----------------------------
DROP TABLE IF EXISTS student;
CREATE TABLE student
(
    id                  int(11)    NOT NULL AUTO_INCREMENT,
    name                varchar(10) NOT NULL COMMENT '姓名',
    sex                 varchar(2)  NOT NULL COMMENT '性别,男、女',
    birth               varchar(20)  NOT NULL COMMENT '出生年月日，例如：2024-01-31',
    organization_id     int(11) NOT NULL COMMENT '对应organization表id',
    create_time         datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_time         datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    delete_time         datetime(3)          DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

-- ----------------------------
-- 测评结果表
-- ----------------------------
DROP TABLE IF EXISTS test_result;
CREATE TABLE test_result
(
    id                           int(11) NOT NULL AUTO_INCREMENT,
    student_id                   int(11) NOT NULL COMMENT '对应student表id',
    organization_id              int(11) NOT NULL COMMENT '对应organization表id',
    auditory_breadth             varchar(20) NOT NULL COMMENT '听觉广度,记录最后正确的组',
    auditory_memory              varchar(20) NOT NULL COMMENT '听觉记忆,记录最后正确的组',
    auditory_stability           int(11) NOT NULL COMMENT '听觉稳定,记录测试的时长',
    auditory_processing          varchar(20) DEFAULT NULL COMMENT '听觉加工,记录最后正确的组',
    auditory_comprehension       int(11) NOT NULL COMMENT '听觉理解,记录错误的数量',
    visual_span_row              int(11) NOT NULL COMMENT '视觉广度、视觉分辨,点到的行数',
    visual_span_num              int(11) NOT NULL COMMENT '视觉广度、视觉分辨,点击到的那一行前面的所有目标图形总量',
    visual_span_leak_num         int(11) NOT NULL COMMENT '视觉广度、视觉分辨,记录错误的数量（漏点）',
    visual_span_error_num        int(11) NOT NULL COMMENT '视觉广度、视觉分辨,记录错误的数量（错点）',
    visual_span_percent          float(11) NOT NULL COMMENT '视觉广度、视觉分辨, 错误的数量/点击到的那一行前面的所有目标图形总量的百分比',
    visual_memory                int(11) NOT NULL COMMENT '视觉记忆,记录最终完成的数量',
    fine_operation_right_num     int(11) NOT NULL COMMENT '精细操作（手控）,记录正确的组数',
    fine_operation_touch_num     int(11) NOT NULL COMMENT '精细操作（手控）,记录碰边的数量',
    visual_processing            int(11) NOT NULL COMMENT '视觉加工,记录孩子正确的数量',
    auditory_breadth_level         int(10) NOT NULL COMMENT '听觉广度, 等级',
    auditory_memory_level          int(10) NOT NULL COMMENT '听觉记忆, 等级',
    auditory_stability_level       int(10) NOT NULL COMMENT '听觉稳定, 等级',
    auditory_processing_level      int(10)          DEFAULT NULL COMMENT '听觉加工, 等级',
    auditory_comprehension_level   int(10) NOT NULL COMMENT '听觉理解, 等级',
    visual_span_level              int(10) NOT NULL COMMENT '视觉广度, 等级',
    visual_discrimination_level    int(10) NOT NULL COMMENT '视觉分辨, 等级',
    visual_memory_level            int(10) NOT NULL COMMENT '视觉记忆, 等级',
    fine_operation_level           int(10) NOT NULL COMMENT '精细操作（手控）, 等级',
    visual_processing_level        int(10) NOT NULL COMMENT '视觉加工, 等级',
    training_start_date            datetime(3)          DEFAULT NULL,
    training_end_date              datetime(3)          DEFAULT NULL,
    dimensional_cycles             varchar(200) DEFAULT NULL COMMENT '',
    auditory_dimensional_cycles    varchar(200) DEFAULT NULL COMMENT '',
    second_vision_dimensions       varchar(200) DEFAULT NULL COMMENT '',
    cycle_count                    int(10) NOT NULL DEFAULT 1 COMMENT '',
    second_vision_cycle_count      int(10) NOT NULL DEFAULT 1 COMMENT '',
    auditory_cycle_count           int(10) NOT NULL DEFAULT 1 COMMENT '',
    create_time                    datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_time                    datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    delete_time                    datetime(3)          DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;


-- ----------------------------
-- 测评标准表
-- ----------------------------
DROP TABLE IF EXISTS test_standard;
CREATE TABLE test_standard
(
    id          int(10) unsigned NOT NULL AUTO_INCREMENT,
    name        varchar(50) NOT NULL COMMENT '维度',
    level_1     varchar(10) NOT NULL COMMENT '1级',
    level_2     varchar(10) NOT NULL COMMENT '2级',
    level_3     varchar(10) NOT NULL COMMENT '3级',
    level_4     varchar(10) NOT NULL COMMENT '4级',
    level_5     varchar(10) NOT NULL COMMENT '5级',
    level_6     varchar(10) NOT NULL COMMENT '6级',
    level_7     varchar(10) NOT NULL COMMENT '7级',
    level_8     varchar(10) NOT NULL COMMENT '8级',
    create_time         datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_time         datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    delete_time         datetime(3)          DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

INSERT INTO test_standard(id, name, level_1, level_2, level_3, level_4, level_5, level_6, level_7, level_8)
VALUES (1, 'auditory_breadth', '4', '5','6','7','8','9','10','11,12');
INSERT INTO test_standard(id, name, level_1, level_2, level_3, level_4, level_5, level_6, level_7, level_8)
VALUES (2, 'auditory_memory', '2', '3','4','5','6','7','8','9');
INSERT INTO test_standard(id, name, level_1, level_2, level_3, level_4, level_5, level_6, level_7, level_8)
VALUES (3, 'auditory_stability', '90', '120','150','180','210','240','270','300');
INSERT INTO test_standard(id, name, level_1, level_2, level_3, level_4, level_5, level_6, level_7, level_8)
VALUES (4, 'auditory_processing', '1', '2','3','4','5','6','7','8,9');
INSERT INTO test_standard(id, name, level_1, level_2, level_3, level_4, level_5, level_6, level_7, level_8)
VALUES (5, 'auditory_comprehension', '7', '6','5','4','3','2','1','0');
INSERT INTO test_standard(id, name, level_1, level_2, level_3, level_4, level_5, level_6, level_7, level_8)
VALUES (6, 'visual_span', '8', '12','16','20','24','28','32','36');
INSERT INTO test_standard(id, name, level_1, level_2, level_3, level_4, level_5, level_6, level_7, level_8)
VALUES (7, 'visual_discrimination', '15', '12','9','7','5','4','3','2');
INSERT INTO test_standard(id, name, level_1, level_2, level_3, level_4, level_5, level_6, level_7, level_8)
VALUES (8, 'visual_memory', '3', '4','5','6','7','8','9','10');
INSERT INTO test_standard(id, name, level_1, level_2, level_3, level_4, level_5, level_6, level_7, level_8)
VALUES (9, 'fine_operation_low', '1-2', '2-3','2-2','3-3','3-2','4-3','4-2','5-3');
INSERT INTO test_standard(id, name, level_1, level_2, level_3, level_4, level_5, level_6, level_7, level_8)
VALUES (10, 'fine_operation_middle', '3-3', '3-2','4-3','4-2','5-3','5-2','6-3','6-2');
INSERT INTO test_standard(id, name, level_1, level_2, level_3, level_4, level_5, level_6, level_7, level_8)
VALUES (11, 'fine_operation_high', '4-2', '5-3','5-2','6-3','6-2','7-3','7-2','7-1');
INSERT INTO test_standard(id, name, level_1, level_2, level_3, level_4, level_5, level_6, level_7, level_8)
VALUES (12, 'visual_processing', '30', '40','50','60','70','80','90','100');

-- ----------------------------
-- 用户-分组表
-- ----------------------------
DROP TABLE IF EXISTS lin_user_group;
CREATE TABLE lin_user_group
(
    id       int(10) unsigned NOT NULL AUTO_INCREMENT,
    user_id  int(10) unsigned NOT NULL COMMENT '用户id',
    group_id int(10) unsigned NOT NULL COMMENT '分组id',
    organization_id     int(11) unsigned DEFAULT NULL COMMENT '对应organization表id',
    PRIMARY KEY (id),
    KEY user_id_group_id (user_id, group_id) USING BTREE COMMENT '联合索引'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

-- ----------------------------
-- 学生训练生成计划表
-- ----------------------------
DROP TABLE IF EXISTS student_training_plan;
CREATE TABLE student_training_plan
(
    id          int(10) unsigned NOT NULL AUTO_INCREMENT,
    organization_id     int(11) unsigned DEFAULT NULL COMMENT '对应organization表id',
    visions        varchar(2000) NOT NULL COMMENT '',
    auditorys     varchar(2000) NOT NULL COMMENT '',
    vision_ids     varchar(100) NOT NULL COMMENT '',
    auditory_ids     varchar(100) NOT NULL COMMENT '',
    student_id       int(11) NOT NULL COMMENT '对应student表id',
    test_result_id   int(11) NOT NULL COMMENT '对应test_result表id',
    training_start_date datetime(3) DEFAULT NULL COMMENT '训练开始时间',
    training_end_date    datetime(3) DEFAULT NULL COMMENT '训练结束时间',
    create_time         datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_time         datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    delete_time         datetime(3)          DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

-- ----------------------------
-- 训练计划表
-- ----------------------------
DROP TABLE IF EXISTS training_plan_library;
CREATE TABLE training_plan_library
(
    id          int(10) unsigned NOT NULL AUTO_INCREMENT,
    name        varchar(10) NOT NULL COMMENT '训练名称',
    desc        varchar(50) NOT NULL COMMENT '训练描述',
    training_level   int(11) NOT NULL COMMENT '训练等级',
    training_file_path     varchar(200) NOT NULL COMMENT '训练方案文件地址',
    training_file_id       int(11) NOT NULL COMMENT '训练方案文件id',
    dimension   varchar(100) NOT NULL COMMENT '维度',
    create_time         datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_time         datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    delete_time         datetime(3)          DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------
-- 插入超级管理员
-- 插入root分组
-- ----------------------------
BEGIN;
INSERT INTO lin_user(id, username, nickname)
VALUES (1, 'root', 'root');

INSERT INTO lin_user_identity (id, user_id, identity_type, identifier, credential)

VALUES (1, 1, 'USERNAME_PASSWORD', 'root',
        'sha1$c419e500$1$84869e5560ebf3de26b6690386484929456d6c07');

INSERT INTO lin_group(id, name, info, level)
VALUES (1, 'root', '超级用户组', 1);

INSERT INTO lin_group(id, name, info, level)
VALUES (2, 'guest', '游客组', 2);

INSERT INTO lin_user_group(id, user_id, group_id,organization_id)
VALUES (1, 1, 1, NULL);

COMMIT;
